import pandas as pd
import pymysql
from pyecharts.charts import Bar, Line, Pie, Map, Timeline, WordCloud, Scatter
from pyecharts import options as opts
from pyecharts.commons.utils import JsCode
from pyecharts.globals import ThemeType
from pyecharts.components import Table
from pyecharts.faker import Faker
from pyecharts.charts import Page

# 数据库连接配置 - 请根据你的实际情况修改
db_config = {
    'host': 'localhost',
    'user': 'root',
    'password': '123456',
    'database': 'ukraine_conflict',
    'port': 3306,
    'charset': 'utf8mb4'
}


def query_data(sql):
    """从MySQL数据库查询数据"""
    connection = pymysql.connect(**db_config)
    try:
        df = pd.read_sql(sql, connection)
    finally:
        connection.close()
    return df


def create_event_trend_chart():
    """创建事件趋势图"""
    sql = """
    SELECT DATE_FORMAT(event_date, '%Y-%m') AS month, 
           COUNT(*) AS event_count
    FROM russia_ukraine_conflict
    GROUP BY month
    ORDER BY month
    """
    df = query_data(sql)

    line = (
        Line(init_opts=opts.InitOpts(theme=ThemeType.DARK, width="100%", height="400px"))
        .add_xaxis(df['month'].tolist())
        .add_yaxis("事件数量", df['event_count'].tolist(),
                   is_smooth=True,
                   label_opts=opts.LabelOpts(is_show=False),
                   linestyle_opts=opts.LineStyleOpts(width=3),
                   symbol_size=8)
        .set_global_opts(
            title_opts=opts.TitleOpts(title="乌克兰冲突事件月度趋势", pos_left="center"),
            tooltip_opts=opts.TooltipOpts(trigger="axis"),
            xaxis_opts=opts.AxisOpts(name="月份", axislabel_opts=opts.LabelOpts(rotate=45)),
            yaxis_opts=opts.AxisOpts(name="事件数量"),
            datazoom_opts=[opts.DataZoomOpts()]
        )
    )
    return line


def create_event_type_pie():
    """创建事件类型分布饼图"""
    sql = """
    SELECT event_type, COUNT(*) AS count
    FROM russia_ukraine_conflict
    GROUP BY event_type
    ORDER BY count DESC
    LIMIT 10
    """
    df = query_data(sql)

    pie = (
        Pie(init_opts=opts.InitOpts(theme=ThemeType.DARK, width="100%", height="400px"))
        .add("", [list(z) for z in zip(df['event_type'], df['count'])],
             radius=["30%", "70%"],
             rosetype="radius")
        .set_global_opts(
            title_opts=opts.TitleOpts(title="冲突事件类型分布", pos_left="center"),
            legend_opts=opts.LegendOpts(orient="vertical", pos_top="15%", pos_left="2%")
        )
        .set_series_opts(label_opts=opts.LabelOpts(formatter="{b}: {c} ({d}%)"))
    )
    return pie


def create_region_distribution_map():
    """创建地区分布地图"""
    sql = """
    SELECT region, COUNT(*) AS event_count
    FROM russia_ukraine_conflict
    GROUP BY region
    """
    df = query_data(sql)

    # 确保地区名称与地图匹配，可能需要转换
    regions = [r.replace('Oblast', '').strip() for r in df['region']]

    map_chart = (
        Map(init_opts=opts.InitOpts(theme=ThemeType.DARK, width="100%", height="400px"))
        .add("事件数量",
             [list(z) for z in zip(regions, df['event_count'])],
             "Ukraine")
        .set_global_opts(
            title_opts=opts.TitleOpts(title="冲突事件地区分布", pos_left="center"),
            visualmap_opts=opts.VisualMapOpts(
                max_=df['event_count'].max(),
                is_piecewise=True,
                range_text=["高", "低"],
                pieces=[
                    {"min": 1000, "label": "1000以上"},
                    {"min": 500, "max": 999, "label": "500-999"},
                    {"min": 100, "max": 499, "label": "100-499"},
                    {"min": 50, "max": 99, "label": "50-99"},
                    {"min": 1, "max": 49, "label": "1-49"},
                    {"value": 0, "label": "无数据"},
                ]
            )
        )
    )
    return map_chart


def create_fatalities_bar():
    """创建伤亡人数柱状图"""
    sql = """
    SELECT 
        YEAR(event_date) AS year,
        MONTH(event_date) AS month,
        SUM(civilian_fatalities) AS civilian,
        SUM(military_fatalities) AS military
    FROM russia_ukraine_conflict
    GROUP BY year, month
    ORDER BY year, month
    """
    df = query_data(sql)
    df['date'] = df['year'].astype(str) + '-' + df['month'].astype(str).str.zfill(2)

    bar = (
        Bar(init_opts=opts.InitOpts(theme=ThemeType.DARK, width="100%", height="400px"))
        .add_xaxis(df['date'].tolist())
        .add_yaxis("平民伤亡", df['civilian'].tolist(), stack="stack1")
        .add_yaxis("军事人员伤亡", df['military'].tolist(), stack="stack1")
        .set_global_opts(
            title_opts=opts.TitleOpts(title="月度伤亡人数统计", pos_left="center"),
            tooltip_opts=opts.TooltipOpts(trigger="axis", axis_pointer_type="shadow"),
            xaxis_opts=opts.AxisOpts(name="月份", axislabel_opts=opts.LabelOpts(rotate=45)),
            yaxis_opts=opts.AxisOpts(name="伤亡人数"),
            datazoom_opts=[opts.DataZoomOpts()]
        )
    )
    return bar


def create_event_source_table():
    """创建事件来源表格"""
    sql = """
    SELECT source, COUNT(*) AS count, 
           SUM(civilian_fatalities) AS civilian_fatalities,
           SUM(military_fatalities) AS military_fatalities
    FROM russia_ukraine_conflict
    GROUP BY source
    ORDER BY count DESC
    LIMIT 10
    """
    df = query_data(sql)

    headers = ["来源", "事件数量", "平民伤亡", "军事伤亡"]
    rows = df.values.tolist()

    table = (
        Table(init_opts=opts.InitOpts(theme=ThemeType.DARK))
        .add(headers, rows)
        .set_global_opts(
            title_opts=opts.TitleOpts(title="主要事件来源统计", subtitle="前10位")
        )
    )
    return table


def create_timeline_chart():
    """创建时间轴图表"""
    sql = """
    SELECT 
        DATE_FORMAT(event_date, '%Y-%m') AS month,
        event_type,
        COUNT(*) AS count
    FROM russia_ukraine_conflict
    GROUP BY month, event_type
    ORDER BY month
    """
    df = query_data(sql)

    timeline = Timeline(init_opts=opts.InitOpts(theme=ThemeType.DARK, width="100%", height="400px"))
    for month in sorted(df['month'].unique()):
        month_data = df[df['month'] == month]
        bar = (
            Bar()
            .add_xaxis(month_data['event_type'].tolist())
            .add_yaxis("事件数量", month_data['count'].tolist())
            .set_global_opts(
                title_opts=opts.TitleOpts(title=f"{month}事件类型分布"),
                xaxis_opts=opts.AxisOpts(axislabel_opts=opts.LabelOpts(rotate=45))
            ))
        timeline.add(bar, month)

    return timeline


def create_dashboard():
    """创建数据大屏"""
    page = Page(
        page_title="乌克兰冲突数据分析大屏",
        layout=Page.DraggablePageLayout,
        js_host="https://assets.pyecharts.org/assets/"
    )

    page.add(
        create_event_trend_chart(),
        create_event_type_pie(),
        create_region_distribution_map(),
        create_fatalities_bar(),
        create_event_source_table(),
        create_timeline_chart()
    )

    # 保存为HTML文件
    page.render("ukraine_conflict_dashboard.html")


if __name__ == "__main__":
    create_dashboard()
    print("数据大屏已生成，请查看ukraine_conflict_dashboard.html文件")